﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;


namespace DoeObjects
{
    /// <summary>
    /// class for the publisher of a title
    /// </summary>
    public class Publisher
    {
        public int PublisherId { get; set; }
        public string Name { get; set; }

        public static Publisher getById(int id)
        {
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand command = new SqlCommand("SELECT * FROM publisher WHERE publisherId = @id", connection);
            command.Parameters.AddWithValue("@id", id);

            Publisher publisher = null;
            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    publisher = getCurrentPublisher(reader);
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return publisher;
        }


        public static List<Publisher> getAll()
        {
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand command = new SqlCommand("SELECT * FROM publisher", connection);

            List<Publisher> publishers = new List<Publisher>();
            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    publishers.Add(getCurrentPublisher(reader));
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return publishers;
        }

        /// <summary>
        /// returns a list of all publishers which matches to the given name
        /// </summary>
        /// <param name="name">the searchterm</param>
        /// <param name="useLikeExpression">set to true if you want to use the sql LIKE expression for searching this name. Otherwise it will return only the publishers which matches exactly on this name</param>
        /// <returns></returns>
        public static List<Publisher> getByName(string name, bool useLikeExpression)
        {
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);

            string sql = null;
            if (useLikeExpression)
            {
                name = name.Replace(' ', '%');
                name = '%' + name + '%';
                sql = "SELECT * FROM [Publisher] WHERE PublisherName LIKE @name";
            }
            else
            {
                sql = "SELECT * FROM [Publisher] WHERE PublisherName = @name";
            }

            SqlCommand command = new SqlCommand(sql, connection);
            SqlParameter nameParameter = new SqlParameter("@name", SqlDbType.VarChar);
            nameParameter.Value = name;
            command.Parameters.Add(nameParameter);

            List<Publisher> publishers = new List<Publisher>();
            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    publishers.Add(getCurrentPublisher(reader));
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return publishers;
        }

        /// <summary>
        /// adds a new publisher into the database
        /// </summary>
        /// <param name="publisher"></param>
        /// <returns></returns>
        public static int add(Publisher publisher)
        {
            int lastInsertedId = 0;
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand("INSERT INTO [Publisher] (PublisherName) OUTPUT INSERTED.publisherId VALUES (@Name)", connection);

            SqlParameter nameParameter = new SqlParameter("@Name", SqlDbType.VarChar);
            nameParameter.Value = publisher.Name;
            cmd.Parameters.Add(nameParameter);

            try
            {
                connection.Open();
                Object res = cmd.ExecuteScalar();
                if (res != null)
                {
                    lastInsertedId = (int)res;
                }
            }
            catch (SqlException er)
            {
                //oo
                throw er;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return lastInsertedId;
        }

        /// <summary>
        /// updates an existing publisher in the database
        /// </summary>
        /// <param name="publisher"></param>
        /// <returns></returns>
        public static bool update(Publisher publisher)
        {
            int affectedRows = 0;
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand("UPDATE [Publisher] SET PublisherName = @Name WHERE publisherId = @Id", connection);
            cmd.Parameters.AddWithValue("@Id", publisher.PublisherId);

            SqlParameter nameParameter = new SqlParameter("@Name", SqlDbType.VarChar);
            nameParameter.Value = publisher.Name;
            cmd.Parameters.Add(nameParameter);

            try
            {
                connection.Open();
                affectedRows = cmd.ExecuteNonQuery();
            }
            catch (SqlException er)
            {
                //oo
                throw er;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return (affectedRows > 0);
        }

        internal static Publisher getCurrentPublisher(SqlDataReader reader)
        {
            Publisher pub = null;

            if (reader != null)
            {
                pub = new Publisher();
                pub.PublisherId = (int)reader["publisherId"];
                pub.Name = reader["PublisherName"] as string;
            }

            return pub;
        }

        /// <summary>
        /// saves the current object to the database. if the id == 0 the an insert will be executed and the id attribute of the object will be updated. otherwise an update will be executed
        /// </summary>
        /// <returns>if the insert/update was successful</returns>
        public bool save()
        {
            if (this.PublisherId == 0)
            {
                this.PublisherId = Publisher.add(this);
                return (this.PublisherId > 0);
            }
            else
            {
                return Publisher.update(this);
            }
        }
    }
}
